﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Language;
using Portal.Environment;

namespace Portal.CDSP
{
    public class FAlbumBLL
    {
        public DataTable ReadingAlbumItems(string AlbumId)
        {

            string Query = "SELECT  TableAlbumItem.AlbumItemId, TableAlbumItem.Subject, TableAlbumItem.fk_Album_Id, TableAlbumItem.fk_AdvImage_Id, TableAlbumItem.MaxNews, " +
                            "           TableAlbumItem.IndexOrder, TableAlbumItem." + Environments.ActiveLanguage + "Subject,TableAlbumItem.Clicked, TableAlbumItem.ShortId, TableAlbumItem.belongSite, " +
                            "           TableAlbum.AlbumType, TableAdvImage." + Environments.ActiveLanguage + "Description" +
                            " FROM       TableAlbumItem INNER JOIN " +
                            "           TableAdvImage ON TableAlbumItem.fk_AdvImage_Id = TableAdvImage.AdvImageId INNER JOIN " +
                            "           TableAlbum ON TableAlbumItem.fk_Album_Id = TableAlbum.AlbumId" +
                            " WHERE      (TableAlbumItem.fk_Album_Id = @AlbumId)";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@AlbumId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AlbumId"].Value = AlbumId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

      

        public DataTable ReadingItems(string AdvImageId)
        {

            string Query =  " select * from TableAdvImageItem where "+
                            " IsPublishing = 1 "+
                            " and ( left(fk_AdvImage_Id,8)=@AdvImageId  or fk_AdvImage_Id=@AdvImageId)  " +                       
                            " order by IndexOrder " 
                            ;

      
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();

            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }
        
        public DataTable ReadingAlbumDefaulView()
        {

            string Query = " select * from TableAdvImageItem,TableAlbumItem where " +
                            " TableAdvImageItem.IsPublishing = 1 " +                            
                            " and  TableAdvImageItem.fk_AdvImage_Id = TableAlbumItem.fk_AdvImage_Id " +
                            " and  TableAlbumItem.setDefaulView = 1 "
                            ;

            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
        
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string GetSubjectAdvImage(string AdvImageId)
        {
            string Query = " select Subject=[" + Environments.ActiveLanguage + "Subject] from TableAdvImage where AdvImageId=@AdvImageId";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;//truyen AlbumId la dung
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                if (dtResult.Rows[0]["Subject"] != DBNull.Value)
                {
                    return "";
                }
                else
                {
                    return (string)dtResult.Rows[0]["Subject"];
                }
            }
        }

        public DataTable Readings(string AdvImageId)
        {
            string Query = "select TableAdvImage.* from TableAdvImage join TableAdvImageItem on TableAdvImageItem.fk_AdvImage_Id=TableAdvImage.AdvImageId where IsPublishing=1 and ( left(fk_AdvImage_Id,8)=@AdvImageId  or fk_AdvImage_Id=@AdvImageId) order by IndexOrder";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingImagesByAlbumId(string AlbumId)
        {
            string Query =

              " select adv.*,ab.vieSubject from TableAdvImageItem adv,TableAdvImage advi,TableAlbum ab,TableAlbumItem abi where " +
              " ab.AlbumId = abi.fk_Album_Id " +
              " and abi.fk_AdvImage_Id = adv.fk_AdvImage_Id " +
              " and advi.AdvImageId  = adv.fk_AdvImage_Id " +
              " and left(fk_Album_Id,8) = @AlbumId " +
              " and IsPublishing = 1 " +
              " and adv.belongSite = 'vie' " +
              " order by IndexOrder " ;

                //"select TableAdvImage.* from TableAdvImage join TableAdvImageItem on TableAdvImageItem.fk_AdvImage_Id=TableAdvImage.AdvImageId where IsPublishing=1 and " +
                //            " and    " +
                //            "   ( left(fk_AdvImage_Id,8)=@AdvImageId  or fk_AdvImage_Id=@AdvImageId) order by IndexOrder";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@AlbumId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AlbumId"].Value = AlbumId;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingAllAlbum(string belongSite)
        {
            string Query = "select *  FROM TableAlbum " +
                              " where belongSite = @belongSite";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public int GetNumImgByAlbumId(string AlbumId)
        {
            Int32 numAlbum = 0;
            using (SqlConnection conn = new SqlConnection(Environments.ConnectionString))
            {
                string Query = " select count(adv.fk_AdvImage_Id) from TableAdvImageItem adv,TableAdvImage advi,TableAlbum ab,TableAlbumItem abi where " +
                                " ab.AlbumId = abi.fk_Album_Id " +
                                " and abi.fk_AdvImage_Id = adv.fk_AdvImage_Id " +
                                " and advi.AdvImageId  = adv.fk_AdvImage_Id " +
                                " and fk_Album_Id = @AlbumId " +
                                " and IsPublishing = 1 " +
                                " and adv.belongSite = 'vie' ";
                                
                SqlCommand cmd = new SqlCommand(Query, conn);
                cmd.Parameters.Add("@AlbumId", SqlDbType.VarChar);
                cmd.Parameters["@AlbumId"].Value = AlbumId;
                try
                {
                    conn.Open();
                    numAlbum = (Int32)cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    numAlbum = 0;
                }
            }
            return (int)numAlbum;
        }      
    }
}